package model;

import db.DB;
import entity.SysUserEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by pc on 2018/4/30.
 */
public class SysUserDao {
    private DB db;
    public SysUserDao() {
        db=new DB();
    }
    List<SysUserEntity> queryUser(Map<String,Object> params) {
        List<SysUserEntity> userList=new ArrayList<SysUserEntity>();
        String sql="select * from sys_user where 1=1";
        if(params.containsKey("username")) {
            String keyVal=params.get("username").toString();
            sql+=" and username like '%"+keyVal+"%'";
        }
        if(params.containsKey("page") && params.containsKey("limit")) {
            Integer page=(Integer)params.get("page");
            Integer limit=(Integer)params.get("limit");
            sql+=" limit "+(page-1)+","+limit;
        }
        System.out.println("查询语句:"+sql);
        ResultSet rs=db.execSelect(sql);
        try {
            while(rs.next()) {
                SysUserEntity user=new SysUserEntity();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                user.setTel(rs.getString("tel"));
                user.setCreateTime(rs.getString("create_time"));
                user.setStatus(rs.getInt("status"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }
    int queryUserCount(Map<String,Object> params) {
        String sql="select count(*) from sys_user where 1=1";
        if(params.containsKey("username")) {
            String keyVal=params.get("username").toString();
            sql+=" and username like '%"+keyVal+"%'";
        }
        ResultSet rs=db.execSelect(sql);
        int count=0;
        try {
            if(rs.next()) {
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    int insertUser(SysUserEntity user) {
        String sql="insert into sys_user(username,password,email,tel,create_time,status) " +
                " values(?,?,?,?,?,?)";
        Connection conn=db.getConn();
        PreparedStatement ps=null;
        int rtn=0;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getEmail());
            ps.setString(4,user.getTel());
            ps.setString(5,user.getCreateTime());
            ps.setInt(6,user.getStatus());
            rtn=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rtn;
    }
    int updateUser(SysUserEntity user) {
        String sql="update sys_user " +
                "set user_name=?,password=?,email=?," +
                "tel=?,create_time=?,status=? " +
                "where user_id=?";
        Connection conn=db.getConn();
        PreparedStatement ps=null;
        int rtn=0;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getEmail());
            ps.setString(4,user.getTel());
            ps.setString(5,user.getCreateTime());
            ps.setInt(6,user.getStatus());
            ps.setInt(7,user.getUserId());
            rtn=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rtn;
    }
    int deleteUser(Integer userId) {
        String sql="delete from sys_user where user_id=?";
        Connection conn=db.getConn();
        PreparedStatement ps=null;
        int rtn=0;
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,userId);
            rtn=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rtn;
    }
    int deleteBatchUser(Integer[] userIds) {
        int len=userIds.length;
        int ii;
        for(ii=0;ii<len;ii++) {
            deleteUser(userIds[ii]);
        }
        return 0;
    }
}
